/**
 * Database inquirer: runs all the queries. 
 * 
 * @author Alican Ozgoren
 * @version 12/14/2012
 */

package edu.brockport.shuttleapp.database;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.ListIterator;
import java.util.Map;
import java.util.Properties;
import java.util.Vector;

import edu.brockport.shuttleapp.MyResult;
import edu.brockport.shuttleapp.Utilities;
import edu.brockport.shuttleapp.exception.EmptyQueryResultException;
import edu.brockport.shuttleapp.exception.UnexpectedProblemException;
import android.content.Context;
import android.database.SQLException;
import android.location.Location;

public class DatabaseInquirer extends DatabaseManager
{	
	private Context myContext = null;
	
	// Selected data
	private String sourceStopId;
	private String destStopId;
	private String selectedTime;
	private String selectedDate;
	private String selectedWeek;
	private Vector<Properties> availableRoutes;
	private static boolean isReverse = false;
	
	//----------------------------------------------------------
	public DatabaseInquirer(Context context) throws UnexpectedProblemException 
	{
		super(context);
		myContext = context;
	}
	
	//----------------------------------------------------------
	public MyResult doYourJob(String sourceStopId, String destStopId, String selectedTime, String selectedDate)
	{
		MyResult myResult = null;
    	int resultCode = 0;
    	Vector resultList = null;
		
		// Initialize selected data
		this.sourceStopId = sourceStopId;
		this.destStopId = destStopId;
		this.selectedTime = selectedTime;
		this.selectedDate = selectedDate;
		
		myResult = getWeekNumber(this.selectedDate);
		resultCode = myResult.getResultCode();
		
		if(resultCode != Utilities.NO_ERROR)
    	{
    		myResult = new MyResult(resultCode, null, myResult.getResultMessage());
    		return myResult;
    	}
		else
		{
			resultList = myResult.getResult();
			selectedWeek = (String) resultList.elementAt(0);
			
			try 
			{
				// Convert the app time to db time
				int dbTime =  Utilities.parseAppTimeToDBTime(selectedTime);
				// Check if the time is 03:04 < dbTime < 06:00
				if(dbTime > 2104 && dbTime <= 2359)
					dbTime = 0;
				this.selectedTime = Integer.toString(dbTime);
				
				// Check the routes if they are available on the selected week
				Vector<Properties> uncheckedRoutes = getScheduleRouteNumbers();
				
				// Group uncheckedRoutes by ScheduleRouteName
				sortD("Departure", uncheckedRoutes);
				groupBy("ScheduleRouteNumber", uncheckedRoutes);
				
				for(int cnt = 0; cnt < uncheckedRoutes.size(); cnt++)
				{
					Properties nextRow = (Properties) uncheckedRoutes.elementAt(cnt);
					myResult = checkWeekDays(nextRow);
					resultCode = myResult.getResultCode();
					
					if(resultCode != Utilities.NO_ERROR)
					{
						myResult = new MyResult(resultCode, null, myResult.getResultMessage());
						return myResult;
					}
					else
					{
						resultList = myResult.getResult();
						boolean checkWeekDays = ((Boolean) resultList.elementAt(0)).booleanValue();
						
						if(!checkWeekDays)
						{
							nextRow.setProperty("Status", "Unavailable");
						}
					}
				}
				
				// Get rid of the unavailable routes
				availableRoutes = removeUnavailableRoutes(uncheckedRoutes);
				
				// Get the route with the maximum runs available
				if(availableRoutes.size() > 0)
				{
					//availableRoute = getMostAvailableRoute(availableRoutes);
					Vector<Properties> runs =  getAvailableRuns();					
	
					return getMostAvailableRuns(runs);
				}
			} 
			catch (EmptyQueryResultException e) 
			{
				myResult = new MyResult(Utilities.EMPTY_QUERY_RESULT, null, "No shuttle available!");
				return myResult;
			}
			catch (ParseException e) 
			{
				myResult = new MyResult(Utilities.UNEXPECTED_ERROR, null, "Unexpected error!");
	    		return myResult;
			}
		}
		
		myResult = new MyResult(Utilities.EMPTY_QUERY_RESULT, null, "No shuttle available!");
		return myResult;
	}
	
	//----------------------------------------------------------
	public Vector getBusStopNames()
	{
        Vector busStops = new Vector();
 
        // Select All Query
        String query = "SELECT * FROM BusStop ORDER BY name";
 
        busStops = getSelectQueryResult(query);
 
        return busStops;
    }
	
	//----------------------------------------------------------
	public Vector getBusStopNames(Location myLocation)
	{
        Vector<Properties> busStops = new Vector<Properties>();
 
        // Select All Query
        String query = "SELECT * FROM BusStop ORDER BY name";
 
        busStops = getSelectQueryResult(query);
        busStops = addDistanceProperty(busStops, myLocation);
 
        return busStops;
    }
	
	//----------------------------------------------------------
	public Vector getRouteInfo(String run)
	{
        Vector busStops = new Vector<Properties>();
 
        // Select All Query
        String query = "SELECT Sequence, name, locationX, locationY " +
        			   "FROM BusStop, RouteStops, Run " +
        			   "WHERE Run._id = " + run + " AND Run.RouteId = " +
        			   "RouteStops.RouteId AND BusStop._id = StopId " +
        			   "ORDER BY Sequence";
 
        busStops = getSelectQueryResult(query);
 
        return busStops;
    }
	
	//----------------------------------------------------------
	public Vector addDistanceProperty(Vector<Properties> busStops, Location myLocation)
	{
        for(Properties row: busStops)
        {
        	String locX = row.getProperty("locationX");
        	String locY = row.getProperty("locationY");
        	if(locX != null && locY != null && !locX.equals("0") && !locY.equals("0"))
        	{
        		double x = Double.valueOf(locX);
            	double y = Double.valueOf(locY);
            	
            	// Set the retrieved bus stop location
            	Location stopLocation = new Location(row.getProperty("_id"));
            	stopLocation.setLatitude(y);
            	stopLocation.setLongitude(x);
            	
            	// Do the distance calculation and add it ass property
            	float distance = getDistance(y, x, myLocation.getLongitude(), myLocation.getLatitude());
            	row.setProperty("Distance", Float.toString(distance));
        	}
        	else
        	{
        		row.setProperty("Distance", "9999000");
        	}
        }
        
        sortDouble("Distance", busStops);
 
        return busStops;
    }
	
	//----------------------------------------------------------
	public static float getDistance(double startx, double starty, double endx, double endy)
	{
	    float[] result = new float[3];
	    Location.distanceBetween(startx, starty, endx, endy, result);
	    return result[0];
	}
	
	//----------------------------------------------------------
	public boolean isShuttleServiceAvailable(String selectedDate)
	{
		String query = "SELECT _id FROM ScheduleStartAndEndDates " + 
					   "WHERE StartDate <= '" + selectedDate + "' AND " + 
					   "EndDate >= '" + selectedDate + "'";
		
		Vector result = getSelectQueryResult(query);
		
		if(result != null && result.size() == 1)
			return true;
		
		return false;
	}
	
	//----------------------------------------------------------
	private Date getStartDay()
	{
		String query = "SELECT StartDate FROM ScheduleStartAndEndDates";
		Vector result = getSelectQueryResult(query);
		
		if(result != null && result.size() == 1)
		{
			Properties row = (Properties) result.elementAt(0);
			String startDate = row.getProperty("StartDate");
			
			try 
			{
				return new SimpleDateFormat("yyyy-MM-dd").parse(startDate);
			} 
			catch (ParseException e) 
			{
				Utilities.toaster(myContext, "Unexpected problem!");
			}
		}
		
		throw new SQLException();
	}
	
	//----------------------------------------------------------
	private MyResult getWeekNumber(String selectedDate)
	{
		MyResult result = null;
		
		Date date = null;
		try 
		{
			date = new SimpleDateFormat("yyyy-MM-dd").parse(selectedDate);
		} 
		catch (ParseException e) 
		{
			result = new MyResult(Utilities.UNEXPECTED_ERROR, null, "Unexpected error!");
			return result;
		}
		
		Calendar startDateCalendar = Calendar.getInstance();
		startDateCalendar.setTime(getStartDay());
		
		Calendar selectedDateCalendar = Calendar.getInstance();
		selectedDateCalendar.setTime(date);
		
		int theWeek = selectedDateCalendar.get(Calendar.WEEK_OF_YEAR);
		int scheduleWeekNumber = 0;
		while(theWeek != startDateCalendar.get(Calendar.WEEK_OF_YEAR)) 
		{
			selectedDateCalendar.add(Calendar.WEEK_OF_YEAR, -1);
			theWeek = selectedDateCalendar.get(Calendar.WEEK_OF_YEAR);
			scheduleWeekNumber++;
		}
		
		Vector resultList = new Vector();
		resultList.add("Week" + scheduleWeekNumber);
		result = new MyResult(Utilities.NO_ERROR, resultList, null);
		
		return result;
	}
	
	//----------------------------------------------------------
	private Vector getScheduleRouteNumbers() throws EmptyQueryResultException
	{
		String query = 
			"SELECT RM.ScheduleRouteNumber, WS." + selectedWeek + ", " +
			"S.ArrivalTime as 'Departure' " +
			"FROM   RunTimes S, RunTimes D, RunMapping RM, WeeklySchedule WS " +
			"WHERE  S.StopId = " + sourceStopId + " AND D.StopId = " + destStopId + 
			" AND S.Sequence < D.Sequence AND S.RunId = D.RunId AND RM.RunId = " +
			"S.RunId AND RM.ScheduleRouteNumber = WS.ScheduleRouteNumber AND " +
		    "WS." + selectedWeek + " != '_______' AND S.ArrivalTime >= '" + selectedTime + "'";
		
		String query_reverse = 
			"SELECT RM.ScheduleRouteNumber, WS." + selectedWeek + ", " +
			"S.ArrivalTime as 'Departure' " +
			"FROM   RunTimes S, RunTimes D, RunMapping RM, WeeklySchedule WS " +
			"WHERE  S.StopId = " + sourceStopId + " AND D.StopId = " + destStopId + 
			" AND (S.RunId + 1) = D.RunId AND RM.RunId = D.RunId AND " +
			"RM.ScheduleRouteNumber = WS.ScheduleRouteNumber AND " +
		    "WS." + selectedWeek + " != '_______' AND S.ArrivalTime >= '" + selectedTime + "'";
		
		Vector result = getSelectQueryResult(query);
		
		if(result != null && result.size() != 0)
		{
			return result;
		}
		else // No results, try the reverse query
		{
			result = getSelectQueryResult(query_reverse);
			
			if(result != null && result.size() != 0)
			{
				return result;
			}
		}
		
		throw new EmptyQueryResultException(myContext, "No shuttle available!");
	}
	
	//----------------------------------------------------------
	private MyResult checkWeekDays(Properties unchechkedRoute)
	{
		MyResult result = null;
		int requestCode = 0;
		Vector resultList = null;
		String resultMessage = null;
		
		String weekDays = unchechkedRoute.getProperty(selectedWeek);
		char[] weekDayArray = weekDays.toCharArray();
		
		// Get the day of the week
		Date date = null;
		try 
		{
			date = new SimpleDateFormat("yyyy-MM-dd").parse(selectedDate);
		} 
		catch (ParseException e) 
		{
			result = new MyResult(Utilities.UNEXPECTED_ERROR, null, "Unexpected Error!");
			return result;
		}
		
		resultList = new Vector();
		Calendar myCalendar = Calendar.getInstance();
		myCalendar.setTime(date);
		int dayOfWeek = myCalendar.get(Calendar.DAY_OF_WEEK);
		
		
		if(weekDayArray[dayOfWeek - 1] == 'X')
		{
			resultList.add(Boolean.valueOf(true));
			result = new MyResult(requestCode, resultList, resultMessage);
			return result;
		}
		
		resultList.add(Boolean.valueOf(false));
		result = new MyResult(requestCode, resultList, resultMessage);
		return result;
	}
	
	//----------------------------------------------------------
	private Vector removeUnavailableRoutes(Vector uncheckedRoutes)
	{
		ListIterator<Properties> runListIterator = uncheckedRoutes.listIterator();
		while(runListIterator.hasNext())
		{
			Properties nextRow = runListIterator.next();
			String nextStatus = nextRow.getProperty("Status");
			if(nextStatus != null && nextStatus.equals("Unavailable"))
				runListIterator.remove();
		}
		
		return uncheckedRoutes;
	}
	
	//----------------------------------------------------------
	private MyResult getMostAvailableRuns(Vector<Properties> runs) throws ParseException
	{	
		MyResult myResult = null;
    	int resultCode = 0;
    	Vector resultList = null;
    	
		for(Properties row: runs)
		{
			// Get original route name
			String runId = row.getProperty("RunId");
			
			myResult = getRouteNumber(runId);
			resultCode = myResult.getResultCode();
			if(resultCode != Utilities.NO_ERROR)
			{
				myResult = new MyResult(resultCode, null, myResult.getResultMessage());
				return myResult;
			}
			else
			{
				resultList = myResult.getResult();
				String rt = (String) resultList.elementAt(0);
				row.setProperty("Route", rt);
			}	
		}
		
		groupBy("ArrivalTime", runs);
		sortInteger("ArrivalTime", runs);
		
		Vector mostAvailableRuns = new Vector();
		int limit = 3;
		for(int cnt = 0; cnt < runs.size(); cnt++)
		{
			mostAvailableRuns.add(runs.elementAt(cnt));
			if(cnt + 1 == limit)
				break;
		}
		
		myResult = new MyResult(Utilities.NO_ERROR, 
				Utilities.convertDBTimeToAppTime(mostAvailableRuns), "Thank you for waiting us!");
		
		return myResult;
	}
	
	//----------------------------------------------------------
	private Vector<Properties> getAvailableRuns() throws EmptyQueryResultException
	{
		String query = "", query_reverse = "";
		Vector<Properties> runs = new Vector<Properties>();
		
		for(Properties row: availableRoutes)
		{
			String route = row.getProperty("ScheduleRouteNumber");
			
			query = 
				"SELECT S.RunId as RunId, S.StopId as Source, D.StopId as Destination, " +
				"S.ArrivalTime as DepartureTime, D.ArrivalTime as ArrivalTime " +
				"FROM   RunTimes S, RunTimes D, RunMapping RM, WeeklySchedule WS " +
				"WHERE  S.StopId = " + sourceStopId + " AND D.StopId = " + destStopId + 
				" AND S.Sequence < D.Sequence AND S.RunId = D.RunId AND RM.RunId = S.RunId " +
				"AND RM.ScheduleRouteNumber = WS.ScheduleRouteNumber AND " +
				"RM.ScheduleRouteNumber = '" + route + "' AND S.ArrivalTime >= '" +
				selectedTime + "' LIMIT 0,3";
				
			query_reverse = 
				"SELECT S.RunId as RunId, S.StopId as Source, D.StopId as Destination, " +
				"S.ArrivalTime as DepartureTime, D.ArrivalTime as ArrivalTime " +
				"FROM   RunTimes S, RunTimes D, RunMapping RM, WeeklySchedule WS " +
				"WHERE  S.StopId = " + sourceStopId + " AND D.StopId = " + destStopId + 
				" AND (S.RunId + 1) = D.RunId AND RM.RunId = S.RunId " +
				"AND RM.ScheduleRouteNumber = WS.ScheduleRouteNumber AND " +
				"RM.ScheduleRouteNumber = '" + route + "' AND S.ArrivalTime >= '" +
				selectedTime + "' LIMIT 0,3";
			
			Vector result = getSelectQueryResult(query);
			
			// If we have any results
			if(result != null && result.size() > 0)
			{
				runs.addAll(result);
				isReverse = false;
			}
			else // No results, try the reverse query
			{
				result = getSelectQueryResult(query_reverse);
				
				if(result != null && result.size() > 0)
				{
					runs.addAll(result);
					isReverse = true;
				}
			}
		}
		
		if(runs != null && runs.size() > 0)
			return runs;
		
		throw new EmptyQueryResultException(myContext, "No shuttle available!");
	}
	
	//----------------------------------------------------------
	public MyResult getRouteNumber(String runId)
	{
		MyResult myResult = null;
    	int resultCode = 0;
    	Vector resultList = null;
    	String resultMessage = null;
		
		// First, get corresponding RouteId
		String query = "SELECT RouteId FROM Run WHERE _id = " + runId;
		Vector result = getSelectQueryResult(query);
		
		String routeId = "";
		if(result != null && result.size() == 1)
		{
			Properties row = (Properties) result.elementAt(0);
			routeId = row.getProperty("RouteId");
		}
		else
		{
			myResult = new MyResult(Utilities.UNEXPECTED_ERROR, null, "Unexpected error!");
			return myResult;
		}
		
		// Now, find the corresponding RouteName
		query = "SELECT RouteName FROM Route WHERE _id = " + routeId;
		result = getSelectQueryResult(query);
		
		if(result != null && result.size() == 1)
		{
			Properties row = (Properties) result.elementAt(0);
			String routeNumber = row.getProperty("RouteName");
			
			resultList = new Vector();
			resultList.add(routeNumber.substring(3, 4));
			myResult = new MyResult(resultCode, resultList, resultMessage);
			
			return myResult;
		}
		
		myResult = new MyResult(Utilities.UNEXPECTED_ERROR, null, "Unexpected error!");
		return myResult;
	}
	
	//----------------------------------------------------------
	public boolean isDatabaseIsEmpty()
	{
		String query = "SELECT * FROM BusStop";
		Vector result = getSelectQueryResult(query);
		
		if(result == null || result.size() < 1)
			return true;
		
		return false;
	}
	
	//----------------------------------------------------------
	private void sortInteger(final String field, Vector<Properties> myVector) 
	{
	    Collections.sort(myVector, new Comparator<Properties>() 
	    {
	        public int compare(Properties o1, Properties o2) 
	        {
	        	String str1 = o1.getProperty(field);
	        	String str2 = o2.getProperty(field);
	        	
	        	int istr1 = Integer.parseInt(str1);
	        	int istr2 = Integer.parseInt(str2);
	        	
	        	if(istr1 > istr2)
	        		return 1;
	        	else
	        	if(istr1 < istr2)
	        		return -1;
	        	
	        	return 0;
	        }           
	    });
	}
	
	//----------------------------------------------------------
	private void sortDouble(final String field, Vector<Properties> myVector) 
	{
	    Collections.sort(myVector, new Comparator<Properties>() 
	    {
	        public int compare(Properties o1, Properties o2) 
	        {
	        	String str1 = o1.getProperty(field);
	        	String str2 = o2.getProperty(field);
	        	
	        	double dstr1 = Double.parseDouble(str1);
	        	double dstr2 = Double.parseDouble(str2);
	        	
	        	if(dstr1 > dstr2)
	        		return 1;
	        	else
	        	if(dstr1 < dstr2)
	        		return -1;
	        	
	        	return 0;
	        }           
	    });
	}
	
	//----------------------------------------------------------
	private void sortD(final String field, Vector<Properties> myVector) 
	{
	    Collections.sort(myVector, new Comparator<Properties>() 
	    {
	        public int compare(Properties o1, Properties o2) 
	        {
	        	String str1 = o1.getProperty(field);
	        	String str2 = o2.getProperty(field);
	        	
	        	return str2.compareTo(str1);
	        }           
	    });
	}
	
	//----------------------------------------------------------
	private void groupBy(String field, Vector<Properties> myVector)
	{
		Map<String, Properties> map = new LinkedHashMap<String, Properties>();
		for(Properties rows : myVector) 
		{
		  map.put(rows.getProperty(field), rows);
		}
		
		myVector.clear();
		myVector.addAll(map.values());
	}
	
	//----------------------------------------------------------
	public static boolean getReverseStatus()
	{
		return isReverse;
	}
	
	// UNUSED CODE
	/*//----------------------------------------------------------
	private void sort(final String field, Vector<Properties> myVector) 
	{
	    Collections.sort(myVector, new Comparator<Properties>() 
	    {
	        public int compare(Properties o1, Properties o2) 
	        {
	        	String str1 = o1.getProperty(field);
	        	String str2 = o2.getProperty(field);
	        	
	        	return str1.compareTo(str2);
	        }           
	    });
	}
	
	//----------------------------------------------------------
	private void sortWithTwoCriteria(final String field1, final String field2, Vector<Properties> myVector) 
	{
	    Collections.sort(myVector, new Comparator<Properties>() 
	    {
	        public int compare(Properties o1, Properties o2) 
	        {
	        	String str1 = o1.getProperty(field1);
	        	String str2 = o2.getProperty(field1);
	        	int result = str1.compareTo(str2);
	        	
	        	if(result == 0)
	        	{
	        		String str3 = o1.getProperty(field2);
		        	String str4 = o2.getProperty(field2);
	        		
		        	return str3.compareTo(str4);
	        	}
	        	else
	        	{
	        		return result;
	        	}	
	        }           
	    });
	}
	
	*/
	
}


